<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="google-site-verification" content="xBT4GhYoi5qRD5tr338pgPM5OWHHIDR6mNg1a3euekI" />
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta name="description" content="">
    <meta name="keyword"  content="Tooi">
    <link rel="shortcut icon" href="/img/favicon.ico">

    <title>
        
        数据库中间件Mycat - Tooi的博客 | Tooi-Blog
        
    </title>

    <!-- Custom CSS -->
    <link rel="stylesheet" href="/css/aircloud.css">
    <link rel="stylesheet" href="/css/gitment.css">
    <!--<link rel="stylesheet" href="https://imsun.github.io/gitment/style/default.css">-->
    <link href="//at.alicdn.com/t/font_620856_pl6z7sid89qkt9.css" rel="stylesheet" type="text/css">
    <!-- ga & ba script hoook -->
    <script></script>
</head>

<body>

<div class="site-nav-toggle" id="site-nav-toggle">
    <button>
        <span class="btn-bar"></span>
        <span class="btn-bar"></span>
        <span class="btn-bar"></span>
    </button>
</div>

<div class="index-about">
    <i> 瞄准月亮。 </i>
</div>

<div class="index-container">
    
    <div class="index-left">
        
<div class="nav" id="nav">
    <div class="avatar-name">
        <div class="avatar ">
            <img src="/img/avatar.jpg" />
        </div>
        <div class="name">
            <i>Tooi6</i>
        </div>
    </div>
    <div class="contents" id="nav-content">
        <ul>
            <li >
                <a href="/">
                    <i class="iconfont icon-shouye1"></i>
                    <span>主页</span>
                </a>
            </li>
            <li >
                <a href="/tags">
                    <i class="iconfont icon-biaoqian1"></i>
                    <span>标签</span>
                </a>
            </li>
            <li >
                <a href="/archives">
                    <i class="iconfont icon-guidang2"></i>
                    <span>存档</span>
                </a>
            </li>
            <li >
                <a href="/about/">
                    <i class="iconfont icon-guanyu2"></i>
                    <span>关于</span>
                </a>
            </li>
            
            <li>
                <a id="search">
                    <i class="iconfont icon-sousuo1"></i>
                    <span>搜索</span>
                </a>
            </li>
            
        </ul>
    </div>
    
        <div id="toc" class="toc-article">
    <ol class="toc"><li class="toc-item toc-level-3"><a class="toc-link" href="#概述"><span class="toc-text">概述</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#官方文档"><span class="toc-text">官方文档</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#什么是Mycat？"><span class="toc-text">什么是Mycat？</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#Mycat的原理？"><span class="toc-text">Mycat的原理？</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#应用场景？"><span class="toc-text">应用场景？</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#DEMO"><span class="toc-text">DEMO</span></a><ol class="toc-child"><li class="toc-item toc-level-5"><a class="toc-link" href="#搭建主从MySQL服务"><span class="toc-text">搭建主从MySQL服务</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#设置主从MySQL"><span class="toc-text">设置主从MySQL</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#配置Mycat"><span class="toc-text">配置Mycat</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#启动、登录Mycat"><span class="toc-text">启动、登录Mycat</span></a></li></ol></li></ol></li></ol>
</div>
    
</div>


<div class="search-field" id="search-field">
    <div class="search-container">
        <div class="search-input">
            <span id="esc-search"> <i class="icon-fanhui iconfont"></i></span>
            <input id="search-input"/>
            <span id="begin-search">搜索</span>
        </div>
        <div class="search-result-container" id="search-result-container">

        </div>
    </div>
</div>

        <div class="index-about-mobile">
            <i> 瞄准月亮。 </i>
        </div>
    </div>
    
    <div class="index-middle">
        <!-- Main Content -->
        


<div class="post-container">
    <div class="post-title">
        数据库中间件Mycat
    </div>

    <div class="post-meta">
        <span class="attr">发布于：<span>2019-12-29 22:38:06</span></span>
        
        <span class="attr">标签：/
        
        <a class="tag" href="/tags/#Mycat" title="Mycat">Mycat</a>
        <span>/</span>
        
        
        </span>
        <span class="attr">访问：<span id="busuanzi_value_page_pv"></span>
</span>
</span>
    </div>
    <div class="post-content ">
        <h3 id="概述"><a href="#概述" class="headerlink" title="概述"></a>概述</h3><h4 id="官方文档"><a href="#官方文档" class="headerlink" title="官方文档"></a>官方文档</h4><blockquote>
<p>官网：<a href="http://www.mycat.io/" target="_blank" rel="noopener">http://www.mycat.io/</a><br>源码：<a href="https://github.com/MyCATApache/Mycat-Server" target="_blank" rel="noopener">https://github.com/MyCATApache/Mycat-Server</a><br>下载地址：<a href="https://github.com/MyCATApache/Mycat-download" target="_blank" rel="noopener">https://github.com/MyCATApache/Mycat-download</a> </p>
</blockquote>
<h4 id="什么是Mycat？"><a href="#什么是Mycat？" class="headerlink" title="什么是Mycat？"></a>什么是Mycat？</h4><blockquote>
<p>Mycat 是<strong>数据库中间件</strong>，就是介于数据库与应用之间，进行数据处理与交互的中间服务，它是一个开源的分布式数据库系统，是一个<strong>实现了 MySQL 协议的的<br>Server</strong>。其核心功能是<strong>分表分库</strong>，即将一个大表水平分割为 N 个小表，存储在后端 MySQL 服务器里或者其他数据库里。<br>对于架构师来说，Mycat是一个强大是<strong>数据库中间件</strong>，不仅可以用作<strong>读写分离、以及分表分库，容灾备份</strong>，而且可以用于多<br>租户应用开发、云平台基础设施、让你的架构具备很强的适应性和灵活性。  </p>
</blockquote>
<h4 id="Mycat的原理？"><a href="#Mycat的原理？" class="headerlink" title="Mycat的原理？"></a>Mycat的原理？</h4><blockquote>
<p>Mycat 的原理中最重要的一个动词是“拦截”，它拦截了用户发送过来的 SQL 语句，首先对 SQL 语句做了<br>一些特定的分析：如分片分析、路由分析、读写分离分析、缓存分析等，然后将此 SQL 发往后端的真实数据库，<br>并将返回的结果做适当的处理，最终再返回给用户  </p>
</blockquote>
<p><img src="https://note.youdao.com/yws/api/personal/file/BA66B38BF33E4D469E104F1C10BE7267?method=download&shareKey=7286c2faad2fb1d577aaa5729b342afd" alt="image">  </p>
<h4 id="应用场景？"><a href="#应用场景？" class="headerlink" title="应用场景？"></a>应用场景？</h4><ul>
<li>单纯的读写分离</li>
<li>分表分库 （对于超过 1000 万的表进行分片，最大支持 1000 亿的单表分片）  </li>
<li>多租户应用 （每个应用一个库，但应用程序只连接 Mycat，从而不改造程序本身，实现多租户化）  </li>
<li>报表系统 （借助于 Mycat 的分表能力，处理大规模报表的统计）  </li>
<li>替代 Hbase，分析大数据</li>
<li>作为海量数据实时查询的一种简单有效方案  </li>
</ul>
<h3 id="DEMO"><a href="#DEMO" class="headerlink" title="DEMO"></a>DEMO</h3><blockquote>
<p>使用Mycat搭建MySQL读写读写分离</p>
</blockquote>
<h5 id="搭建主从MySQL服务"><a href="#搭建主从MySQL服务" class="headerlink" title="搭建主从MySQL服务"></a><strong>搭建主从MySQL服务</strong></h5><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br></pre></td><td class="code"><pre><span class="line"># 使用docker-compose搭建</span><br><span class="line"></span><br><span class="line"># Master</span><br><span class="line"># docker-compose.yml</span><br><span class="line">version: &apos;3&apos;</span><br><span class="line">services:</span><br><span class="line">  master:</span><br><span class="line">    restart: always</span><br><span class="line">    image: mysql:5.7.22</span><br><span class="line">    container_name: master</span><br><span class="line">    ports:</span><br><span class="line">      - 3306:3306</span><br><span class="line">    environment:</span><br><span class="line">      TZ: Asia/Shanghai</span><br><span class="line">      MYSQL_ROOT_PASSWORD: 123456</span><br><span class="line">    command:</span><br><span class="line">      --character-set-server=utf8mb4</span><br><span class="line">      --collation-server=utf8mb4_general_ci</span><br><span class="line">      --explicit_defaults_for_timestamp=true</span><br><span class="line">      --lower_case_table_names=1</span><br><span class="line">      --max_allowed_packet=128M</span><br><span class="line">      --sql-mode=&quot;STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO&quot;</span><br><span class="line">    volumes:</span><br><span class="line">      - ./conf/my.cnf:/etc/my.cnf</span><br><span class="line">      - mysql-data:/var/lib/mysql</span><br><span class="line">volumes:</span><br><span class="line">  mysql-data:</span><br><span class="line"></span><br><span class="line"># my.cnf</span><br><span class="line">[mysqld]</span><br><span class="line">user=mysql</span><br><span class="line">default-storage-engine=INNODB</span><br><span class="line">character-set-server=utf8</span><br><span class="line">server-id=1   # 设置server-id，注意唯一</span><br><span class="line">log-bin=mysql-bin   # 开启二进制日志功能，以备Slave作为其它Slave的Master时使用</span><br><span class="line">binlog-ignore-db=mysql   # 设置忽略数据库</span><br><span class="line">binlog-ignore-db=information_schema    </span><br><span class="line">binlog-do-db=testdb   # 设置需要负责的数据库</span><br><span class="line">binlog_format=STATEMENT</span><br><span class="line">[client]</span><br><span class="line">default-character-set=utf8</span><br><span class="line">[mysql]</span><br><span class="line">default-character-set=utf8</span><br><span class="line"></span><br><span class="line"># Slave </span><br><span class="line"># docker-compose.yml 相同</span><br><span class="line"># my.cnf</span><br><span class="line">[mysqld]</span><br><span class="line">user=mysql</span><br><span class="line">default-storage-engine=INNODB</span><br><span class="line">character-set-server=utf8</span><br><span class="line">server-id=2   # server-id,唯一</span><br><span class="line">relay-log=mysql-relay  # 启动中继日志  </span><br><span class="line">[client]</span><br><span class="line">default-character-set=utf8</span><br><span class="line">[mysql]</span><br><span class="line">default-character-set=utf8</span><br></pre></td></tr></table></figure>

<h5 id="设置主从MySQL"><a href="#设置主从MySQL" class="headerlink" title="设置主从MySQL"></a><strong>设置主从MySQL</strong></h5><ul>
<li>Master</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"># 创建数据同步用户，并授权（Slave使用该用户进行复制）  </span><br><span class="line">CREATE USER &apos;slave&apos;@&apos;%&apos; IDENTIFIED BY &apos;123456&apos;;</span><br><span class="line">GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO &apos;slave&apos;@&apos;%&apos;;  </span><br><span class="line"># 查看master状态</span><br><span class="line">show master status;</span><br></pre></td></tr></table></figure>
<p><img src="https://note.youdao.com/yws/api/personal/file/C61B852927594DC0865B54B05BFBAA45?method=download&shareKey=d968103cbc05bff70d6c862a1916c8a8" alt="image">  </p>
<ul>
<li>Slave</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"># 登录MySQL</span><br><span class="line">mysql -uroot -p  </span><br><span class="line"></span><br><span class="line"># 设置Master信息（执行下面语句）</span><br><span class="line">change master to master_host=&apos;192.168.213.141&apos;, </span><br><span class="line">master_user=&apos;slave&apos;, </span><br><span class="line">master_password=&apos;123456&apos;, </span><br><span class="line">master_port=3306, </span><br><span class="line">master_log_file=&apos;mysql-bin.000001&apos;, </span><br><span class="line">master_log_pos= 154, </span><br><span class="line">master_connect_retry=30;</span><br><span class="line"></span><br><span class="line"># 开启主从复制</span><br><span class="line">start slave</span><br><span class="line"># 查看同步状态</span><br><span class="line">show slave status \G;</span><br></pre></td></tr></table></figure>
<blockquote>
<p><strong>master_host</strong>： Master的地址<br><strong>master_port</strong>：Master的端口号<br><strong>master_user</strong>：用于数据同步的用户<br><strong>master_password</strong>：用于同步的用户的密码<br><strong>master_log_file</strong>：指定 Slave 从哪个日志文件开始复制数据，即上面图片中提到的 File 字段的值<br><strong>master_log_pos</strong>：从哪个 Position 开始读，即上面图片的Position字段的值<br><strong>master_connect_retry</strong>：如果连接失败，重试的时间间隔，单位是秒，默认是60秒  </p>
</blockquote>
<p><img src="https://note.youdao.com/yws/api/personal/file/E4B1471721084C63824C193ABD2EB493?method=download&shareKey=bc0785d65224ecef9fee445411cd404f" alt="image">  </p>
<blockquote>
<p>SlaveIORunning 和 SlaveSQLRunning 都为yes则设置成功，否则查看下面的错误信息。  </p>
</blockquote>
<h5 id="配置Mycat"><a href="#配置Mycat" class="headerlink" title="配置Mycat"></a>配置Mycat</h5><blockquote>
<p>详细配置请查看官网的权威文档</p>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br></pre></td><td class="code"><pre><span class="line"># 修改用户信息（conf/server.xml）</span><br><span class="line">&lt;user name=&quot;mycat&quot;&gt;</span><br><span class="line">    &lt;property name=&quot;password&quot;&gt;123456&lt;/property&gt;</span><br><span class="line">    &lt;property name=&quot;schemas&quot;&gt;TESTDB&lt;/property&gt;</span><br><span class="line">&lt;/user&gt;</span><br><span class="line"></span><br><span class="line"># 修改 conf/schema.xml 文件（只保留下面内容）</span><br><span class="line">&lt;?xml version=&quot;1.0&quot;?&gt;</span><br><span class="line">&lt;!DOCTYPE mycat:schema SYSTEM &quot;schema.dtd&quot;&gt;</span><br><span class="line">&lt;mycat:schema xmlns:mycat=&quot;http://io.mycat/&quot;&gt;</span><br><span class="line"></span><br><span class="line">        &lt;schema name=&quot;TESTDB&quot; checkSQLschema=&quot;false&quot; sqlMaxLimit=&quot;100&quot; dataNode=&quot;dn1&quot; &gt;</span><br><span class="line">        &lt;/schema&gt;</span><br><span class="line">        &lt;dataNode name=&quot;dn1&quot; dataHost=&quot;host1&quot; database=&quot;db1&quot; /&gt;</span><br><span class="line">        &lt;dataHost name=&quot;host1&quot; maxCon=&quot;1000&quot; minCon=&quot;10&quot; balance=&quot;3&quot;</span><br><span class="line">                          writeType=&quot;0&quot; dbType=&quot;mysql&quot; dbDriver=&quot;native&quot; switchType=&quot;1&quot;  slaveThreshold=&quot;100&quot;&gt;</span><br><span class="line">                &lt;heartbeat&gt;select user()&lt;/heartbeat&gt;</span><br><span class="line">                &lt;!-- can have multi write hosts --&gt;</span><br><span class="line">                &lt;writeHost host=&quot;hostM1&quot; url=&quot;192.r168.213.141:3306&quot; user=&quot;root&quot;</span><br><span class="line">                                   password=&quot;123456&quot;&gt;</span><br><span class="line">                        &lt;!-- can have multi read hosts --&gt;</span><br><span class="line">                        &lt;readHost host=&quot;hostS1&quot; url=&quot;192.168.213.142:3306&quot; user=&quot;root&quot; password=&quot;123456&quot; /&gt;</span><br><span class="line">                &lt;/writeHost&gt;</span><br><span class="line">        &lt;/dataHost&gt;</span><br><span class="line">&lt;/mycat:schema&gt;</span><br></pre></td></tr></table></figure>

<h5 id="启动、登录Mycat"><a href="#启动、登录Mycat" class="headerlink" title="启动、登录Mycat"></a>启动、登录Mycat</h5><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"># 控制台启动</span><br><span class="line">./mycat console</span><br><span class="line"></span><br><span class="line"># 后台启动</span><br><span class="line">./mycat start</span><br><span class="line"></span><br><span class="line"># 登录数据窗口  </span><br><span class="line">mysql -umycat -p123456 -P 8066 -h 192.168.213.140</span><br><span class="line"></span><br><span class="line"># 登录后台管理窗口 </span><br><span class="line">mysql -umycat -p123456 -P 9066 -h 192.168.213.140</span><br></pre></td></tr></table></figure>





        
            <div class="donate-container">
    <div class="donate-button">
        <button id="donate-button">赞赏</button>
    </div>
    <div class="donate-img-container hide" id="donate-img-container">
        <img id="donate-img" src="" data-src="/img/donate.png">
        <p> 感谢鼓励 </p>
    </div>
</div>
        
        <br />
        <div id="comment-container">
        </div>
        <div id="disqus_thread"></div>

        <div id="lv-container">
        </div>

    </div>
</div>

    </div>
</div>


<footer class="footer">
    <ul class="list-inline text-center">
        
        

        

        

        
        <li>
            <a target="_blank"  href="https://github.com/Tooi6">
                            <span class="fa-stack fa-lg">
                                <i class="iconfont icon-github"></i>
                            </span>
            </a>
        </li>
        

        

    </ul>
    
    <p>
        <span>/</span>
        
        <span><a href="https://github.com/Tooi6" target="_blank" rel="noopener">Tooi6</a></span>
        <span>/</span>
        
        <span><a href="#">Tooi-Blog</a></span>
        <span>/</span>
        
        <span><a href="#">It helps SEO</a></span>
        <span>/</span>
        
    </p>
    
    <p>
        <span id="busuanzi_container_site_pv">
            <span id="busuanzi_value_site_pv"></span>PV
        </span>
        <span id="busuanzi_container_site_uv">
            <span id="busuanzi_value_site_uv"></span>UV
        </span>
        Created By <a href="https://hexo.io/" target="_blank" rel="noopener">Hexo</a>  Theme <a href="https://github.com/aircloud/hexo-theme-aircloud" target="_blank" rel="noopener">AirCloud</a></p>
</footer>




</body>

<script>
    // We expose some of the variables needed by the front end
    window.hexo_search_path = "search.json"
    window.hexo_root = "/"
    window.isPost = true
</script>
<script src="https://cdn.bootcss.com/jquery/3.3.1/jquery.min.js"></script>
<script src="/js/index.js"></script>
<script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>




</html>
